package com.supos.progress.excel;

import com.supos.progress.util.Constants;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.DecimalFormat;
import java.util.*;

public class AnalysisExcel {
    @Deprecated
    public static String readXls(String path) {
        String text = "";
        try {
            FileInputStream inputStream = new FileInputStream(Constants.filePath + path);
            HSSFWorkbook excel = new HSSFWorkbook(inputStream);
            //获取第一个sheet
            HSSFSheet sheet0 = excel.getSheetAt(0);
            for (Iterator rowIterator = sheet0.rowIterator(); rowIterator.hasNext(); ) {
                HSSFRow row = (HSSFRow) rowIterator.next();
                for (Iterator iterator = row.cellIterator(); iterator.hasNext(); ) {
                    HSSFCell cell = (HSSFCell) iterator.next();
                    //根据单元的类型，读取相应的结果
                    if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                        text += cell.getStringCellValue() + "\t";
                    } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                        text += cell.getNumericCellValue() + "\t";
                    } else if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
                        text += cell.getCellFormula() + "\t";
                    }
                }
                text += "\n";
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return text;
    }

    public static List<Map<String, Object>> readXlsx(String path) {
        List<Map<String, Object>> list = new ArrayList<>();
        XSSFWorkbook excel = readExcel(path);
        XSSFSheet sheet0 = excel.getSheetAt(0);
        for (int rowNum = 1; rowNum <= sheet0.getLastRowNum(); rowNum++) {
            Map<String, Object> jsonObject = new HashMap<>();
            XSSFRow row = sheet0.getRow(rowNum);
            if (row == null) {
                continue;
            }
            XSSFCell cell = row.getCell(0);
            jsonObject.put("name", cell.getStringCellValue());
            cell = row.getCell(1);
            jsonObject.put("time", cell.getNumericCellValue());
            list.add(jsonObject);
        }

        sortList(list);
        return list;
    }

    private static XSSFWorkbook readExcel(String path) {
        XSSFWorkbook excel = null;
        try {
            OPCPackage opcPackage = OPCPackage.open(Constants.filePath + "/excel/" + path);
            excel = new XSSFWorkbook(opcPackage);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        }
        return excel;
    }

    private static List<Map<String, Object>> sortList(List<Map<String, Object>> list) {
        Collections.sort(list, (r1, r2) -> {
            Double time1 = (Double) r1.get("time");
            Double time2 = (Double) r2.get("time");
            return time2.compareTo(time1);
        });
        return list;
    }

    public static List<Map<String, Object>> getGroupTime(String path) {
        XSSFWorkbook excel = readExcel(path);
        XSSFSheet sheet = excel.getSheetAt(0);
        TreeMap<String, List<Double>> treeMap = new TreeMap<>();
        for (int rowNum = 1; rowNum < sheet.getLastRowNum(); rowNum++) {
            XSSFRow row = sheet.getRow(rowNum);
            if (row == null) {
                continue;
            }

            XSSFCell cell = row.getCell(0);
            String name = cell.getStringCellValue();
            if (name.equals("阮志坚")) {
                continue;
            }
            cell = row.getCell(2);
            String group = cell.getStringCellValue();
            cell = row.getCell(1);
            double time = cell.getNumericCellValue();
            group = group == null ? "default" : group;

            //分组
            List<Double> groups = treeMap.get(group);
            if (groups == null) {
                groups = new ArrayList<>();
            }
            groups.add(time);
            treeMap.put(group, groups);
        }

        return averGroupTime(treeMap);
    }

    private static List<Map<String, Object>> averGroupTime(TreeMap<String, List<Double>> treeMap) {
        List<Map<String, Object>> list = new ArrayList<>();
        Map<String, Double> map = new HashMap<>();
        for (String key : treeMap.keySet()) {
            List<Double> groups = treeMap.get(key);
            double average = groups.stream().mapToDouble(r -> r).average().getAsDouble();
            DecimalFormat df = new DecimalFormat("#.00");
            double finalAverage = (double) Math.round(average * 100) / 100;
            map.put(key, finalAverage);
        }
        List<Map.Entry<String, Double>> entry = new ArrayList<>(map.entrySet());
        entry = sortMapValue(entry);
        for (Iterator<Map.Entry<String, Double>> iterator = entry.iterator(); iterator.hasNext(); ) {
            Map.Entry<String, Double> group = iterator.next();
            list.add(new HashMap<String, Object>() {{
                put(group.getKey(), group.getValue());
            }});
        }
        return list;
    }

    private static List<Map.Entry<String, Double>> sortMapValue(List<Map.Entry<String, Double>> entry) {
        Collections.sort(entry, (r1, r2) -> {
            Double time1 = r1.getValue();
            Double time2 = r2.getValue();
            return time2.compareTo(time1);
        });
        return entry;
    }
}
